<?php

class Model_ZendDb_CategoryService implements Model_CategoryService 
{
	/**
     * @var Zend_Db_Adapter_Abstract
     */
    private $_db;
    
    /**
     * @var Model_ZendDb_Dbtable_Category
     */
    private $_category;

    /**
     * @var Model_ZendDb_DbTable_CategoriesUsers
     */
    private $_categoriesUsers;
    
    /**
     * @var 
     */
    private $_categoriesOfInterest;

    public function __construct()
    {
        $this->_category = new Model_ZendDb_DbTable_Category();
        $this->_categoriesUsers = new Model_ZendDb_DbTable_CategoriesUsers();
        $this->_db = $this->_category->getAdapter();
        $this->_categoriesOfInterest = new Model_ZendDb_DbTable_CategoriesOfInterest();
    }
    
    /**
     * Check if a category already exists
     * 
     * @param String $name
     * @return Model_Entity_Category if the category $name is present  
     */
    private function isCategoryPresent($name) {
        $select = $this->_db->quoteInto('name = ?', $name);
        $result = $this->_category->fetchAll($select);
        $cat = null;
        
        if (! 0 == count($result)) {
            $cat = new Model_Entity_Category($result->current()->toArray());
        }
        
        return $cat;
    }
    
    /**
     * Check if $category is already owned by $category->user
     * 
     * @param Model_Entity_Category $category 
     * @return boolean
     */
    private function isCategoryAlreadyOwned($category) {
        $select = $this->_categoriesUsers->select();
        $select->where('user_oid = ?', $category->getCreatedBy()->getOid())  
               ->where('category_oid = ?', $category->getOid());
        
        $result = $this->_categoriesUsers->fetchAll($select);
        
        $isPresent = true;
        if (0 == count($result)) {
            $isPresent = false;
        }
        
        return $isPresent;      
    }
    
    /**
     * Check if a category has no feed
     * 
     * @param Model_Entity_Category $category 
     * @return boolean     
     */
    private function isCategoryEmpty(Model_Entity_Category $category) {
        $select = $this->_db->select();
        
        $category_oid = $this->_db->quote($category->getOid());
        $owner = $category->getOwners();
        
        $sql = "SELECT COUNT(*) AS elements FROM category_feed ".
               "JOIN user_feed AS uf ON uf.feed_oid = category_feed.feed_oid ";
        
        if (!empty($owner)) {
            $user_oid = $this->_db->quote($owner->getOid());
            $where = "WHERE uf.user_oid = $user_oid AND category_feed.category_oid = $category_oid";
        } else {
            $where = "WHERE category_feed.category_oid = $category_oid";
        }
               
        $sql .= $where;
        
        $result = $this->_db->fetchAll($sql);
        
        return $result[0]['elements'] == 0 ? true : false;
    }
    
    /**
     * Check if a category has no owners
     * 
     * @param int $catID 
     * @return boolean     
     */
    private function hasCategoryOwners($catID) {
        $select = $this->_db->select();
        
        $sql = "SELECT COUNT(*) AS elements FROM categories_users ".
               "WHERE category_oid = $catID";
        
        $result =  $this->_db->fetchAll($sql);

        return $result[0]['elements'] == 0 ? false : true;
    }
    
    /**
     * Check if a category is in the preferred list of any user
     * 
     * @param $catID
     * @return boolean
     */
    private function isCategoryPreferred($catID) {
        $select = $this->_db->select();
        
        $sql = "SELECT COUNT(*) AS elements FROM categoriesofinterest ".
               "WHERE category_oid = $catID";
        
        $result = $this->_db->fetchAll($sql);
        
        return $result[0]['elements'] == 0 ? false : true;
    }
    
    /**
     * Fetch the top ten categories
     * 
     * @return array of Model_Entity_Category
     * @see application/models/Model_CategoryService#getTopTenCategories()
     */
    public function getTopTenCategories() {
    	$sql = "SELECT category.oid, category.name, count(*) AS quantity FROM category ".
               "JOIN categories_users AS cu ON category.oid = cu.category_oid ".
               "GROUP BY category.oid ".
               "ORDER BY quantity DESC ".
               "LIMIT 10";
    	
    	$resultSet = $this->_db->fetchAll($sql);
    	
    	$cateogries = array();
    	foreach($resultSet as $row) {
    		$cateogries[] = new Model_Entity_Category($row);
    	}
    	
    	return $cateogries;
    }
    
   /**
     * Fetch all user categories
     * 
     * @param int $userID
     * @return array of Model_Entity_Category
     * @see application/models/Model_FeedService#getCategories()
     */
    public function getUserCategories($userID) {
        $select = $this->_db->select();
        
        $select->from('category')
               ->join(array('cu' => 'categories_users'), 
                            'cu.category_oid = category.oid', 
                            'cu.description')
               ->where('cu.user_oid = ?', $userID);
                       
        $resultSet = $this->_db->fetchAll($select);
        
        $entries = array();
        foreach ($resultSet as $row) {
            $entries[] = new Model_Entity_Category($row);
        }
        
        return $entries;
    }

    /**
     * Retrieve all categories
     * A wrapper to searchCategories
     * 
     * @return array of Model_Entity_Category
     * @see application/models/Model_CategoryService#getCategories()
     */    
    public function getCategories() {
    	return $this->searchCategories()->getItems(null, null);
    }
    
    /**
     * Retrieve informations of a category
     * 
     * @see application/models/Model_FeedService#getCategroy($category)
     * @return Model_Entity_Category
     */
    public function getCategory(Model_Entity_Category $category) {
        $result = $this->_category->find($category->oid);
        
        $cat = null;
        
        if (! 0 == count($result)) {
            $select = $this->_categoriesUsers->select();
            
            $select->where('category_oid = ?', $category->oid);
            $select->where('user_oid = ?', $category->owners->oid);
            
            $cat = new Model_Entity_Category($result->current()->toArray());
            
            $descRow = $this->_categoriesUsers->fetchRow($select);
            if (! 0 == count($descRow)) {
                $descData = $descRow->toArray();            
                $cat->setDescription($descData['description']);
            }
        }
        
        return $cat;
    }
    
   /**
     * Add a new category
     * 
     * @param Model_Entity_Category $category
     * @see application/models/Model_FeedService#addCategory($category)
     * @return void
     */
    public function addCategory(Model_Entity_Category $category) {
        $catID = 0;
        $catData = $category->getOptions();
        
        $catData['created_by'] = $category->getCreatedBy()->getOid();
        
        unset($catData['oid']);
        unset($catData['feeds']);
        unset($catData['owners']);
        unset($catData['createdBy']);
        unset($catData['description']);
        
        try {
            $this->_db->beginTransaction();
            
            $catSearch = $this->isCategoryPresent($category->name);
            // Check if category is already present 
            if (null == $catSearch) {
                $catID = $this->_category->insert($catData);
            }
            
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
            
            throw $e;
        }
    }
    
    /**
     * Add a new user category
     * 
     * @param Model_Entity_Category $category
     * @see application/models/Model_FeedService#addCategory($category)
     * @return void
     */
    public function addUserCategory(Model_Entity_Category $category) {
        $catID = 0;
        $catData = $category->getOptions();
        
        $catData['created_by'] = $category->getCreatedBy()->getOid();
        
        unset($catData['oid']);
        unset($catData['feeds']);
        unset($catData['owners']);
        unset($catData['createdBy']);
        unset($catData['description']);
        
        try {
            $this->_db->beginTransaction();
            
            $catSearch = $this->isCategoryPresent($category->name);
            // Check if category is already present 
            if (null == $catSearch) {
                $catID = $this->_category->insert($catData);
            } else {
                $catID = $catSearch->oid; // If so reuse it!
            }

            $category->setOid($catID);
            
            // Check if the user already has the category being added
            if (!$this->isCategoryAlreadyOwned($category)) {
                $this->_categoriesUsers->insert(array(
                    'category_oid' => $catID,
                    'user_oid'     => $catData['created_by'],
                    'description'  => $category->getDescription()
                ));
            }
            
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
            
            throw $e;
        }
    }

    /**
     * Update informatio of an existing user category
     * 
     * @param Model_Entity_Category $category
     * @see application/models/Model_FeedService#updateCategory($category)
     * @return void
     */
    public function updateUserCategory(Model_Entity_Category $category) {
        //$catData = $category->getOptions();
        $catData = array();
        
        // Update only category description
        $catData['description'] = $category->description;
        
        $this->_categoriesUsers->update($catData, 
              array('category_oid = ?' => $category->oid,
                    'user_oid = ?'     => $category->owners->oid
              ));
    }

    /**
     * Remove a category from it's owner only if has no feed associated
     * 
     * @param Model_Entity_Category $category
     * @return void
     * @see application/models/Model_FeedService#deleteUserCategory($category)
     */
    public function deleteUserCategory(Model_Entity_Category $category) {
        if (!$this->isCategoryEmpty($category)) {
            throw new Model_ZendDb_CantDeleteException('cathasfeeds');
        }

        $this->_categoriesUsers->delete(
            array('category_oid = ?' => $category->oid,
                  'user_oid = ?'     => $category->owners->oid)
        );
    }
    
    /**
     * Remove an existing category only if has no feed associated and is not
     * a preferred category
     * 
     * @param Model_Entity_Category $category
     * @see application/models/Model_FeedService#deleteCategory($category)
     * @return void
     */
    public function deleteCategory(Model_Entity_Category $category) {
        if (!$this->isCategoryEmpty($category) ||
              $this->hasCategoryOwners($category->oid) ||
              $this->isCategoryPreferred($category->oid)) {
            throw new Model_ZendDb_CantDeleteException();       
        }
        
        $this->_category->delete(
            array('oid = ?' => $category->oid)
        );
    }
    
    /**
     * Search for category matching search criteria based on category and
     * contents of name and description fields
     * If no criteria are specified it will return the entire list of categories
     * in the system
     * 
     * @param array $criteria an optional associative array  
     * @return Zend_Paginator_Adapter_Interface
     * @see application/models/Model_FeedService#searchCategories($criteria)
     */
    public function searchCategories($criteria = null) {
        $select = $this->_db->select();
        $partialWhere ='';
        
        $select->from('category');
        
        if ($criteria !== null) {
            if (isset($criteria['userID']) && !empty($criteria['userID'])) {
                /*$select->join(array('cu' => 'categories_users'), 
                              'cu.category_oid = category.oid', 
                              'cu.description')
                       ->where('cu.user_oid = ?', $criteria['userID']);
                       
                $partialWhere.= "UPPER(cu.description) like UPPER(?) or ";*/
                $select->where('created_by = ?', $criteria['userID']);
            }

            if (isset($criteria['contains']) && !empty($criteria['contains'])) {
                $contains = '%'.$criteria['contains'].'%';
                
                $partialWhere.= "UPPER(category.name) like UPPER(?)";
                $select->where($partialWhere, $contains);
            }
        }
        //die($select->__toString());
        return new Model_ZendDb_Paginator_SearchCategory($select);
    }
    
    /**
     * Set a given category as interesting for a given user 
     * 
     * @param int $catID
     * @param int $userID
     * @return void     
     * @see application/models/Model_CategoryService#setCategoryOfInterest($catID, $userID)
     */
    public function setCategoryOfInterest($catID, $userID) {
    	$this->_categoriesOfInterest->insert(array(
                    'category_oid' => $catID,
                    'user_oid'     => $userID
    	));
    }
    
    /**
     * Unset a given category as interesting for a given user 
     * 
     * @param int $catID
     * @param int $userID
     * @return void
     * @see application/models/Model_CategoryService#removeCategoryOfInterest($catID, $userID)
     */
    public function removeCategoryOfInterest($catID, $userID) {
    	$this->_categoriesOfInterest->delete(array(
                    'category_oid = ?' => $catID,
                    'user_oid = ?'     => $userID
        ));
    }
    
    /**
     * Retrieve the list of interesting categories for a given user
     * 
     * @param int $userID
     * @return array of Model_Entity_Category
     * @see application/models/Model_CategoryService#getCategoriesOfInterest($userID)
     */  
    public function getCategoriesOfInterest($userID) {
    	$select = $this->_db->select();
    	$select->from('category')
    	       ->join(array('cof' => 'categoriesofinterest'), 
    	                    'category.oid = cof.category_oid', null)
    	       ->where('cof.user_oid = ?', $userID);
    	       
    	$resultSet = $this->_db->fetchAll($select);
    	
    	$categories = array();
    	foreach($resultSet as $row) {
    		$categories[] = new Model_Entity_Category($row);
    	}
    	
    	return $categories;
    }
}
